package cn.company.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cn.company.bean.AboutUs;
import cn.company.bean.Advertising;
import cn.company.bean.Contact;
import cn.company.bean.Culture;
import cn.company.bean.HoldClient;
import cn.company.bean.Module;
import cn.company.bean.ProduceService;
import cn.company.bean.Scheme;
import cn.company.bean.Sort;
import cn.company.bean.SubSort;

/**
 * 数据库管理类
 * @author mzba
 *
 */
public class DBUtils {
	private static Connection conn = null;
	private static Statement stm = null;
	private static ResultSet rs = null;
	private static Connections conns = new Connections();
	
	
	/**
	 * 获取所有留言
	 * @return
	 */
	public static List<Contact> getContacts(){
		List<Contact> contacts = new ArrayList<Contact>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from contact");
			while(rs.next()){
				Contact contact = new Contact();
				contact.setId(rs.getInt("id"));
				contact.setTopic(rs.getString("topic"));
				contact.setEmail(rs.getString("email"));
				contact.setName(rs.getString("name"));
				contact.setPhone(rs.getString("phone"));
				contact.setContent(rs.getString("content"));
				contact.setTime(rs.getString("time"));
				contacts.add(contact);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return contacts;
	}
	
	/**
	 * 根据Id获取留言
	 * @param id
	 * @return
	 */
	public static Contact getContactById(int id){
		Contact contact = new Contact();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from contact where id="+id+"");
			while(rs.next()){
				contact.setId(rs.getInt("id"));
				contact.setTopic(rs.getString("topic"));
				contact.setEmail(rs.getString("email"));
				contact.setName(rs.getString("name"));
				contact.setPhone(rs.getString("phone"));
				contact.setTime(rs.getString("time"));
				contact.setContent(rs.getString("content"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return contact;
	}
	
	/**
	 * 根据Id获取分类
	 * @param id
	 * @return
	 */
	public static Sort getSortById(int id){
		Sort sort = new Sort();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from sort where id="+id+"");
			while(rs.next()){
				sort.setId(rs.getInt("id"));
				sort.setModuleId(rs.getInt("moduleid"));
				sort.setSortName(rs.getString("sortname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return sort;
	}
	
	/**
	 * 获取所有的现有客户
	 * @return
	 */
	public static List<HoldClient> getHoldClients(){
		List<HoldClient> clients = new ArrayList<HoldClient>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from clients order by taxis");
			while(rs.next()){
				HoldClient client = new HoldClient();
				client.setId(rs.getInt("id"));
				client.setName(rs.getString("name"));
				client.setContent(rs.getString("content"));
				client.setImagepath(rs.getString("imagepath"));
				client.setTime(rs.getString("time"));
				client.setSortid(rs.getString("sortid"));
				client.setTaxis(rs.getInt("taxis"));
				client.setUrl(rs.getString("url"));
				clients.add(client);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return clients;
	}
	
	/**
	 * 根据id获取现有客户
	 * @param id
	 * @return
	 */
	public static HoldClient getHoldClientById(int id){
		HoldClient client = new HoldClient();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from clients where id="+id+"");
			while(rs.next()){
				client.setId(rs.getInt("id"));
				client.setName(rs.getString("name"));
				client.setContent(rs.getString("content"));
				client.setImagepath(rs.getString("imagepath"));
				client.setTime(rs.getString("time"));
				client.setSortid(rs.getString("sortid"));
				client.setTaxis(rs.getInt("taxis"));
				client.setUrl(rs.getString("url"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return client;
	}
	
	/**
	 * 添加现有客户
	 * @param clientname
	 * @param clientimagepath
	 * @param describe
	 * @return
	 */
	public static int addHoldClient(String clientname,String clientimagepath,String content,String url,String sortid, String taxis){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 hh时mm分");
		String time = sdf.format(new Date());
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into clients (name,imagepath,content,url,sortid,time, taxis) values ('"+clientname+"','"+clientimagepath+"','"+content+"','"+url+"','"+sortid+"','"+time+"'," + taxis + ")";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 修改现有客户
	 * @param id
	 * @param taxis
	 * @param clientname
	 * @param clientimagepath
	 * @param describe
	 * @return
	 */
	public static int updateHoldClient(int id,int taxis,String clientname,String clientimagepath,String content,String url,String sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update clients set taxis="+taxis+",name='"+clientname+"'," +
					" imagepath='"+clientimagepath+"',content='"+content+"',url='"+url+"',sortid='"+sortid+"' where id="+id+"";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	/**
	 * 获取所有的一级产品服务
	 * @return
	 */
	public static List<ProduceService> getProduceService(){
		List<ProduceService> produceservices = new ArrayList<ProduceService>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from product where isSecondTree = 0");
			while(rs.next()){
				ProduceService produceservice = new ProduceService();
				produceservice.setId(rs.getInt("id"));
				produceservice.setTopic(rs.getString("topic"));
				produceservice.setImagepath(rs.getString("imagepath"));
				produceservice.setContent(rs.getString("content"));
				produceservice.setTime(rs.getString("time"));
				produceservice.setSortid(rs.getInt("sortid"));
				produceservices.add(produceservice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return produceservices;
	}
	
	/**
	 * 获取所有的产品服务
	 * @return
	 */
	public static List<ProduceService> getAllProduceService(){
		List<ProduceService> produceservices = new ArrayList<ProduceService>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from product");
			while(rs.next()){
				ProduceService produceservice = new ProduceService();
				produceservice.setId(rs.getInt("id"));
				produceservice.setTopic(rs.getString("topic"));
				produceservice.setImagepath(rs.getString("imagepath"));
				produceservice.setContent(rs.getString("content"));
				produceservice.setTime(rs.getString("time"));
				produceservice.setSortid(rs.getInt("sortid"));
				produceservices.add(produceservice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return produceservices;
	}
	
	/**
	 * 根据id获取产品服务
	 * @param id
	 * @return
	 */
	public static ProduceService getProduceServiceById(int id){
		ProduceService produceservice = new ProduceService();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from product where id="+id+"");
			while(rs.next()){
				produceservice.setId(rs.getInt("id"));
				produceservice.setTopic(rs.getString("topic"));
				produceservice.setImagepath(rs.getString("imagepath"));
				produceservice.setContent(rs.getString("content"));
				produceservice.setTime(rs.getString("time"));
				produceservice.setSortid(rs.getInt("sortid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return produceservice;
	}
	
	/**
	 * 获取所有的一级产品服务
	 * @return
	 */
	public static List<ProduceService> getProduceSecondService() {
		List<ProduceService> produceservices = new ArrayList<ProduceService>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm
					.executeQuery("select * from product where isSecondTree = 1");
			while (rs.next()) {
				ProduceService produceservice = new ProduceService();
				produceservice.setId(rs.getInt("id"));
				produceservice.setTopic(rs.getString("topic"));
				produceservice.setImagepath(rs.getString("imagepath"));
				produceservice.setContent(rs.getString("content"));
				produceservice.setTime(rs.getString("time"));
				produceservice.setSortid(rs.getInt("sortid"));
				produceservices.add(produceservice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (stm != null) {
					stm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return produceservices;
	}
	
	/**
	 * 根据一级菜单的id获取其下的所有产品服务
	 * @param id
	 * @return
	 */
	public static List<ProduceService> getProduceSecondServiceById(int id) {
		List<ProduceService> produceservices = new ArrayList<ProduceService>();
		ProduceService produceservice;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from product where sortid=" + id + "");
			while (rs.next()) {
				produceservice = new ProduceService();
				produceservice.setId(rs.getInt("id"));
				produceservice.setTopic(rs.getString("topic"));
				produceservice.setImagepath(rs.getString("imagepath"));
				produceservice.setContent(rs.getString("content"));
				produceservice.setTime(rs.getString("time"));
				produceservice.setSortid(rs.getInt("sortid"));
				produceservices.add(produceservice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (stm != null) {
					stm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return produceservices;
	}
	
	/**
	 * 添加产品服务
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @return
	 */
	public static int addProduceService(String topic,String imagepath,String content, boolean isSecondTree,int sortid){
		int result = 0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 hh时mm分");
		String time = sdf.format(new Date());
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into product (topic,imagepath,content,time,sortid,isSecondTree) values ('"+topic+"'," +
					"'"+imagepath+"','"+content+"','"+time+"',"+sortid+"," + isSecondTree + ")";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 修改产品服务
	 * @param id
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @return
	 */
	public static int updateProductService(int id,String topic,String imagepath,String content,int sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update product set topic='"+topic+"',imagepath='"+imagepath+"',content='"+content+"',sortid="+sortid+" where id="+id+"";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 获取关于我们
	 * @return
	 */
	public static List<AboutUs> getAbountUs(){
		List<AboutUs> abouts = new ArrayList<AboutUs>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from about");
			while(rs.next()){
				AboutUs about = new AboutUs();
				about.setContent(rs.getString("content"));
				about.setId(rs.getInt("id"));
				about.setImagepath(rs.getString("imagepath"));
				about.setSortid(rs.getInt("sortid"));
				about.setTime(rs.getString("time"));
				about.setTopic(rs.getString("topic"));
				abouts.add(about);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return abouts;
	}
	
	/**
	 * 根据Id获取关于我们
	 * @param id
	 * @return
	 */
	public static AboutUs getAboutUsById(int id){
		AboutUs about = new AboutUs();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from about where id="+id+"");
			while(rs.next()){
				about.setContent(rs.getString("content"));
				about.setId(rs.getInt("id"));
				about.setImagepath(rs.getString("imagepath"));
				about.setSortid(rs.getInt("sortid"));
				about.setTime(rs.getString("time"));
				about.setTopic(rs.getString("topic"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return about;
	}
	/**
	 * 添加关于我们
	 * @param sortname
	 * @param content
	 * @return
	 */
	public static int addAboutUs(String topic,String content,String imagepath,int sortid){
		int result = 0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 hh时mm分");
		String time = sdf.format(new Date());
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into about (topic,content,time,imagepath,sortid) values ('"+topic+"','"+content+"','"+time+"','"+imagepath+"',"+sortid+")";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	/**
	 * 修改关于我们
	 * @param id
	 * @param sortname
	 * @param content
	 * @return
	 */
	public static int updateAboutUs(int id,String topic,String content,String imagepath,int sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update about set topic='"+topic+"',content='"+content+"',imagepath='"+imagepath+"',sortid="+sortid+" where id="+id+"";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	/**
	 * 获取模块
	 * @return
	 */
	public static List<Module> getModules(){
		List<Module> modules = new ArrayList<Module>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from module");
			while(rs.next()){
				Module module = new Module();
				module.setContent(rs.getString("content"));
				module.setId(rs.getInt("id"));
				module.setName(rs.getString("name"));
				modules.add(module);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return modules;
	}
	
	/**
	 * 获取分类
	 * @return
	 */
	public static List<Sort> getSorts(){
		List<Sort> sorts = new ArrayList<Sort>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from sort");
			while(rs.next()){
				Sort sort = new Sort();
				sort.setId(rs.getInt("id"));
				sort.setModuleId(rs.getInt("moduleid"));
				sort.setSortName(rs.getString("sortname"));
				sorts.add(sort);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return sorts;
	}
	/**
	 * 添加分类
	 * @param sortname
	 * @param moduleid
	 * @return
	 */
	public static int addSort(String sortname,int moduleid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into sort (sortname,moduleid) values ('"+sortname+"','"+moduleid+"')";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 更新分类
	 * @param sortname
	 * @param moduleid
	 * @return
	 */
	public static int updateSort(String sortname,int moduleid, int id){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update sort set sortname = '" + sortname + "',moduleid = '"+moduleid+"' where id = " + id;
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 根据id获取模块名称
	 * @param id
	 * @return
	 */
	public static String getModuleNameById(int id){
		String moduleName = "";
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from module where id="+id+"";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				moduleName = rs.getString("name");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return moduleName;
	}
	/**
	 * 根据模块id获取分类
	 * @param moduleid
	 * @return
	 */
	public static List<Sort> getSortsByModuleId(int moduleid){
		List<Sort> sorts = new ArrayList<Sort>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from sort where moduleid="+moduleid+"");
			while(rs.next()){
				Sort sort = new Sort();
				sort.setId(rs.getInt("id"));
				sort.setModuleId(rs.getInt("moduleid"));
				sort.setSortName(rs.getString("sortname"));
				sorts.add(sort);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return sorts;
	} 
	
	/**
	 * 根据id获取分类名称
	 * @param id
	 * @return
	 */
	public static String getSortNameById(int id){
		String sortName = "";
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from sort where id="+id+"";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				sortName = rs.getString("sortname");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return sortName;
	}
	
	/**
	 * 根据id获取企业子分类名称
	 * @param id
	 * @return
	 */
	public static String getSubSortNameById(int id){
		String sortName = "";
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from subsort where id="+id+"";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				sortName = rs.getString("subsortname");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return sortName;
	}
	
	/**
	 * 获取企业文化
	 * @return
	 */
	public static List<Culture> getCultrues(){
		List<Culture> cultures = new ArrayList<Culture>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from culture order by time desc");
			while(rs.next()){
				Culture culture = new Culture();
				culture.setId(rs.getInt("id"));
				culture.setTopic(rs.getString("topic"));
				culture.setContent(rs.getString("content"));
				culture.setImagepath(rs.getString("imagepath"));
				culture.setSortid(rs.getInt("subsortid"));
				culture.setTime(rs.getString("time"));
				cultures.add(culture);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return cultures;
	}
	
	/**
	 * 根据子分类id获取企业文化
	 * @return
	 */
	public static List<Culture> getCultruesBySortId(int id){
		List<Culture> cultures = new ArrayList<Culture>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from culture where subsortid="+id+" order by time desc");
			while(rs.next()){
				Culture culture = new Culture();
				culture.setId(rs.getInt("id"));
				culture.setTopic(rs.getString("topic"));
				culture.setContent(rs.getString("content"));
				culture.setImagepath(rs.getString("imagepath"));
				culture.setSortid(rs.getInt("subsortid"));
				culture.setTime(rs.getString("time"));
				cultures.add(culture);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return cultures;
	}
	
	/**
	 * 根据id获取企业文化
	 * @param id
	 * @return
	 */
	public static Culture getCultureById(int id){
		Culture culture = new Culture();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from culture where id="+id+" order by time desc");
			while(rs.next()){
				culture.setId(rs.getInt("id"));
				culture.setTopic(rs.getString("topic"));
				culture.setContent(rs.getString("content"));
				culture.setImagepath(rs.getString("imagepath"));
				culture.setSortid(rs.getInt("subsortid"));
				culture.setTime(rs.getString("time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return culture;
	}
	
	/**
	 * 修改企业文化
	 * @param id
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @param sortid
	 * @return
	 */
	public static int updateCulture(int id,String topic,String imagepath,String content,String time,int sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update culture set topic='"+topic+"',imagepath='"+imagepath+"',content='"+content+"',time='"+time+"',subsortid="+sortid+" where id="+id+"";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 添加企业文化
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @param sortid
	 * @return
	 */
	public static int addCulture(String topic,String imagepath,String content,String time,int sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into culture (topic,imagepath,content,time,subsortid) values ('"+topic+"'," +
					"'"+imagepath+"','"+content+"','"+time+"',"+sortid+")";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 获取解决方案
	 * @return
	 */
	public static List<Scheme> getSchemes(){
		List<Scheme> schemes = new ArrayList<Scheme>();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from scheme");
			while(rs.next()){
				Scheme scheme = new Scheme();
				scheme.setId(rs.getInt("id"));
				scheme.setTopic(rs.getString("topic"));
				scheme.setContent(rs.getString("content"));
				scheme.setImagepath(rs.getString("imagepath"));
				scheme.setSortid(rs.getInt("sortid"));
				scheme.setTime(rs.getString("time"));
				scheme.setLongTopic(rs.getString("longtopic"));
				schemes.add(scheme);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return schemes;
	}
	
	/**
	 * 根据id获取解决方案
	 * @param id
	 * @return
	 */
	public static Scheme getSchemeById(int id){
		Scheme scheme = new Scheme();
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from scheme where id="+id+"");
			while(rs.next()){
				scheme.setId(rs.getInt("id"));
				scheme.setTopic(rs.getString("topic"));
				scheme.setContent(rs.getString("content"));
				scheme.setImagepath(rs.getString("imagepath"));
				scheme.setSortid(rs.getInt("sortid"));
				scheme.setTime(rs.getString("time"));
				scheme.setLongTopic(rs.getString("longtopic"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return scheme;
	}
	
	/**
	 * 修改解决方案
	 * @param id
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @param sortid
	 * @return
	 */
	public static int updateScheme(int id,String topic,String longtopic,String imagepath,String content,int sortid){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "update scheme set topic='"+topic+"',longtopic='"+longtopic+"',imagepath='"+imagepath+"',content='"+content+"',sortid="+sortid+" where id="+id+"";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 添加解决方案
	 * @param topic
	 * @param imagepath
	 * @param content
	 * @param sortid
	 * @return
	 */
	public static int addScheme(String topic,String longtopic,String imagepath,String content,int sortid){
		int result = 0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String time = sdf.format(new Date());
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into scheme (topic,longtopic,imagepath,content,time,sortid) values ('"+topic+"','"+longtopic+"'," +
					"'"+imagepath+"','"+content+"','"+time+"',"+sortid+")";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 根据分类名称获取企业文化
	 * @param sortname
	 * @return
	 */
	public static List<Culture> getCultureBySortName(String sortname){
		//todo
		List<Culture> lists = new ArrayList<Culture>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from culture where subsortid=(select id from sort where sortname='"+sortname+"')";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				Culture culture = new Culture();
				culture.setId(rs.getInt("id"));
				culture.setTopic(rs.getString("topic"));
				culture.setContent(rs.getString("content"));
				culture.setImagepath(rs.getString("imagepath"));
				culture.setSortid(rs.getInt("subsortid"));
				culture.setTime(rs.getString("time"));
				lists.add(culture);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据分类名称获取关于我们
	 * @param sortname
	 * @return
	 */
	public static List<AboutUs> getAboutBySortName(String sortname){
		List<AboutUs> lists = new ArrayList<AboutUs>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from about where sortid=(select id from sort where sortname='"+sortname+"')";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				AboutUs about = new AboutUs();
				about.setId(rs.getInt("id"));
				about.setTopic(rs.getString("topic"));
				about.setContent(rs.getString("content"));
				about.setImagepath(rs.getString("imagepath"));
				about.setSortid(rs.getInt("sortid"));
				about.setTime(rs.getString("time"));
				lists.add(about);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据分类名称获取产品服务
	 * @param sortname
	 * @return
	 */
	public static List<ProduceService> getProductBySortName(int id){
		List<ProduceService> lists = new ArrayList<ProduceService>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from product where sortid="+id+"";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				ProduceService product = new ProduceService();
				product.setId(rs.getInt("id"));
				product.setTopic(rs.getString("topic"));
				product.setContent(rs.getString("content"));
				product.setImagepath(rs.getString("imagepath"));
				product.setSortid(rs.getInt("sortid"));
				product.setTime(rs.getString("time"));
				lists.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据分类名称获取现有客户
	 * @param sortname
	 * @return
	 */
	public static List<HoldClient> getClientBySortId(int id){
		List<HoldClient> lists = new ArrayList<HoldClient>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from clients where LOCATE('"+id+"', sortid) order by taxis";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				HoldClient client = new HoldClient();
				client.setId(rs.getInt("id"));
				client.setName(rs.getString("name"));
				client.setContent(rs.getString("content"));
				client.setImagepath(rs.getString("imagepath"));
				client.setSortid(rs.getString("sortid"));
				client.setTime(rs.getString("time"));
				client.setSortid(rs.getString("sortid"));
				client.setTaxis(rs.getInt("taxis"));
				client.setUrl(rs.getString("url"));
				lists.add(client);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据分类名称获取解决方案
	 * @param sortname
	 * @return
	 */
	public static List<Scheme> getSchemeBySortName(String sortname){
		List<Scheme> lists = new ArrayList<Scheme>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from scheme where sortid=(select id from sort where sortname='"+sortname+"')";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				Scheme scheme = new Scheme();
				scheme.setId(rs.getInt("id"));
				scheme.setTopic(rs.getString("topic"));
				scheme.setContent(rs.getString("content"));
				scheme.setImagepath(rs.getString("imagepath"));
				scheme.setSortid(rs.getInt("sortid"));
				scheme.setTime(rs.getString("time"));
				scheme.setLongTopic(rs.getString("longtopic"));
				lists.add(scheme);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 获取广告
	 * @return
	 */
	public static List<Advertising> getAdvs(){
		List<Advertising> lists = new ArrayList<Advertising>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from advertising";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				Advertising adv = new Advertising();
				adv.setId(rs.getInt("id"));
				adv.setAdvName(rs.getString("name"));
				adv.setType(rs.getString("type"));
				adv.setImagepath(rs.getString("imagepath"));
				lists.add(adv);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据类型获取广告
	 * @return
	 */
	public static List<Advertising> getAdvsByType(String type){
		List<Advertising> lists = new ArrayList<Advertising>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from advertising where type='"+type+"'";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				Advertising adv = new Advertising();
				adv.setId(rs.getInt("id"));
				adv.setAdvName(rs.getString("name"));
				adv.setType(rs.getString("type"));
				adv.setImagepath(rs.getString("imagepath"));
				lists.add(adv);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 添加广告
	 * @param name
	 * @param imagepath
	 * @param type
	 * @return
	 */
	public static int addAdvertising(String name,String imagepath,String type){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into advertising (name,imagepath,type) values ('"+name+"'," +
					"'"+imagepath+"','"+type+"')";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 添加企业文化子分类
	 * @param sortid
	 * @param subsortName
	 * @return
	 */
	public static int addSubSort(int sortid,String subsortName){
		int result = 0;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "insert into subsort (sortid,subsortname) values ("+sortid+"," +
					"'"+subsortName+"')";
			result = stm.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 获取所有的子分类
	 * @return
	 */
	public static List<SubSort> getSubSorts(){
		List<SubSort> lists = new ArrayList<SubSort>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from subsort";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				SubSort subsort = new SubSort();
				subsort.setId(rs.getInt("id"));
				subsort.setSortid(rs.getInt("sortid"));
				subsort.setSubsortName(rs.getString("subsortname"));
				lists.add(subsort);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	}
	
	/**
	 * 根据分类id获取子分类
	 * @param sortid
	 * @return
	 */
	public static List<SubSort> getSubSortsBySortId(int sortid){
		List<SubSort> lists = new ArrayList<SubSort>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from subsort where sortid="+sortid+"";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				SubSort subsort = new SubSort();
				subsort.setId(rs.getInt("id"));
				subsort.setSortid(rs.getInt("sortid"));
				subsort.setSubsortName(rs.getString("subsortname"));
				lists.add(subsort);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	} 
	
	/**
	 * 根据大分类获的名称取企业文化(用于首页显示图片为非空)
	 * @param sortname
	 * @return
	 */
	public static List<Culture> getCulturesByCustom(String sortname){
		List<Culture> lists = new ArrayList<Culture>();
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from culture where subsortid in (select id from subsort where sortid in (select id from sort where sortname='"+sortname+"'))";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				if(!rs.getString("imagepath").equals("")){
					Culture culture = new Culture();
					culture.setId(rs.getInt("id"));
					culture.setTopic(rs.getString("topic"));
					culture.setContent(rs.getString("content"));
					culture.setImagepath(rs.getString("imagepath"));
					culture.setSortid(rs.getInt("subsortid"));
					culture.setTime(rs.getString("time"));
					lists.add(culture);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return lists;
	} 
	
	/**
	 * 根据企业文化id获取分类id
	 * @return
	 */
	public static int getSortIdBySubSort(int id){
		int result = 0;
		ResultSet rs = null;
		try {
			conn = conns.getConnetion();
			stm = conn.createStatement();
			String sql = "select * from sort where id = (select sortid from subsort where id=(select subsortid from culture where id="+id+"))";
			rs = stm.executeQuery(sql);
			while(rs.next()){
				result = rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if(stm != null){
					stm.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return result;
	}
}
